package com.TeacherInfoServer.mapper;

import com.TeacherInfoServer.pojo.DTO.SearchDTO.TeacherSearchDTO;
import com.TeacherInfoServer.pojo.entity.Teacher;
import com.TeacherInfoServer.pojo.vo.TeacherListVo;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * 教师信息mapper接口
 */
public interface TeacherMapper extends BaseMapper<Teacher> {
    @Select("SELECT * FROM user ORDER BY LEFT(teacher_id, 1)")
    List<Teacher> selectAllUsersOrderByFirstLetter();

    @Select("SELECT t.*, jt.value as jobTitle, d.name as department, deg.value as degree, eth.value as ethnic, edu.value as educated, col.value as college " +
            "FROM teacher t " +
            "LEFT JOIN type_info jt ON t.job_title_id = jt.id " +
            "LEFT JOIN department d ON t.department_id = d.id " +
            "LEFT JOIN type_info deg ON t.degree_id = deg.id " +
            "LEFT JOIN type_info eth ON t.ethnic_id = eth.id " +
            "LEFT JOIN type_info edu ON t.educated_id = edu.id " +
            "LEFT JOIN type_info col ON t.college_id = col.id " +
            "ORDER BY LEFT(t.teacher_name, 1) " )
    Page<TeacherListVo> getTeacherDTOListByPage(Page<?> page, @Param("qw") QueryWrapper<Teacher> queryWrapper);
    @Select("SELECT t.*, jt.value as jobTitle, d.name as department, deg.value as degree, eth.value as ethnic, edu.value as educated, col.value as college " +
            "FROM teacher t " +
            "LEFT JOIN type_info jt ON t.job_title_id = jt.id " +
            "LEFT JOIN department d ON t.department_id = d.id " +
            "LEFT JOIN type_info deg ON t.degree_id = deg.id " +
            "LEFT JOIN type_info eth ON t.ethnic_id = eth.id " +
            "LEFT JOIN type_info edu ON t.educated_id = edu.id " +
            "LEFT JOIN type_info col ON t.college_id = col.id " +
            "ORDER BY LEFT(t.teacher_name, 1) " )
    List<TeacherListVo> getTeacherDTOList();

    @Select("<script>" +
            "SELECT t.*, jt.value as jobTitle, d.name as department, deg.value as degree, eth.value as ethnic, edu.value as educated, col.value as college " +
            "FROM teacher t " +
            "LEFT JOIN type_info jt ON t.job_title_id = jt.id " +
            "LEFT JOIN department d ON t.department_id = d.id " +
            "LEFT JOIN type_info deg ON t.degree_id = deg.id " +
            "LEFT JOIN type_info eth ON t.ethnic_id = eth.id " +
            "LEFT JOIN type_info edu ON t.educated_id = edu.id " +
            "LEFT JOIN type_info col ON t.college_id = col.id " +
            "<where> " +
            "<if test='search.teacherId != null and search.teacherId.trim() != \"\"'> " +
            "AND t.teacher_id LIKE CONCAT('%', #{search.teacherId}, '%') " +
            "</if> " +
            "<if test='search.teacherName != null and search.teacherName.trim() != \"\"'> " +
            "AND t.teacher_name LIKE CONCAT('%', #{search.teacherName}, '%') " +
            "</if> " +
            "<if test='search.gender != null and search.gender.trim() != \"\"'> " +
            "AND t.gender = #{search.gender} " +
            "</if> " +
            "<if test='search.birthDate != null and search.birthDate.trim() != \"\"'> " +
            "AND t.birth_date = #{search.birthDate} " +
            "</if> " +
            "<if test='search.idCardNumber != null and search.idCardNumber.trim() != \"\"'> " +
            "AND t.id_card_number LIKE CONCAT('%', #{search.idCardNumber}, '%') " +
            "</if> " +
            "<if test='search.jobTitle != null and search.jobTitle.trim() != \"\"'> " +
            "AND jt.value LIKE CONCAT('%', #{search.jobTitle}, '%') " +
            "</if> " +
            "<if test='search.department != null and search.department.trim() != \"\"'> " +
            "AND d.name LIKE CONCAT('%', #{search.department}, '%') " +
            "</if> " +
            "<if test='search.degree != null and search.degree.trim() != \"\"'> " +
            "AND deg.value LIKE CONCAT('%', #{search.degree}, '%') " +
            "</if> " +
            "<if test='search.educated != null and search.educated.trim() != \"\"'> " +
            "AND edu.value LIKE CONCAT('%', #{search.educated}, '%') " +
            "</if> " +
            "<if test='search.ethnic != null and search.ethnic.trim() != \"\"'> " +
            "AND eth.value LIKE CONCAT('%', #{search.ethnic}, '%') " +
            "</if> " +
            "<if test='search.college != null and search.college.trim() != \"\"'> " +
            "AND col.value LIKE CONCAT('%', #{search.college}, '%') " +
            "</if> " +
            "<if test='search.age != null and search.age.trim() != \"\"'> " +
            "AND t.age LIKE CONCAT('%', #{search.age}, '%') " +
            "</if> " +
            "</where> " +
            "ORDER BY LEFT(t.teacher_name, 1)" +
            "</script>")
    Page<TeacherListVo> searchTeacherList(Page<?> page, @Param("search") TeacherSearchDTO search);

}
